package it.okkam.longwell.ui.command;

import java.io.*;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.Properties;
import java.util.Set;
import java.util.SortedSet;
import java.util.TreeSet;
import java.util.Vector;

import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import javax.servlet.ServletContext;

import org.apache.log4j.Logger;
import org.apache.velocity.VelocityContext;
import org.apache.velocity.exception.MethodInvocationException;
import org.apache.velocity.exception.ParseErrorException;
import org.apache.velocity.exception.ResourceNotFoundException;

import com.hp.hpl.jena.query.Dataset;
import com.hp.hpl.jena.query.Query;
import com.hp.hpl.jena.query.QueryExecution;
import com.hp.hpl.jena.query.QueryExecutionFactory;
import com.hp.hpl.jena.query.QueryFactory;
import com.hp.hpl.jena.query.QuerySolution;
import com.hp.hpl.jena.query.ResultSet;
import com.hp.hpl.jena.rdf.model.Model;
import com.hp.hpl.jena.tdb.TDBFactory;
import com.hp.hpl.jena.tdb.store.Hash;

import edu.mit.simile.longwell.LongwellServer;
import edu.mit.simile.longwell.LongwellServlet;
import edu.mit.simile.longwell.Profile;
import edu.mit.simile.longwell.corpus.Corpus;
import edu.mit.simile.longwell.corpus.TDBConnector;
import edu.mit.simile.longwell.login.CheckLogin;
import edu.mit.simile.longwell.query.Restriction;
import edu.mit.simile.longwell.query.bucket.Bucket;
import edu.mit.simile.longwell.query.bucket.BucketTheme;
import edu.mit.simile.longwell.schema.IStructuredSchema;
import edu.mit.simile.longwell.ui.FlairMessage;
import edu.mit.simile.longwell.ui.FlairURL;
import edu.mit.simile.longwell.ui.InjectionManager;
import edu.mit.simile.longwell.ui.command.CommandBase;

/**
 * 
 * @author nicola
 * 
 */



public class DomandeCompetenzaCommand extends CommandBase {
	private static File graphsMappingFile;
	final static private Logger s_logger = Logger
			.getLogger(DomandeCompetenzaCommand.class);
	VelocityContext vcContext = null;

	/**
	 * @param injectionManager
	 */
	public DomandeCompetenzaCommand(InjectionManager injectionManager) {
		super(injectionManager);

	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * edu.mit.simile.flair.IFlairCommand#execute(edu.mit.simile.flair.FlairMessage
	 * )
	 */
	synchronized public void execute(FlairMessage msg) throws ServletException {
		VelocityContext vcContext = createContext(msg);
		HttpSession session = msg.m_request.getSession();
		String[] dataset = (String[]) msg.m_request.getSession().getAttribute(msg.m_request.getSession().getId());
		String type = msg.m_request.getParameter("type");
		String sessionId = session.getId();
		Profile profile = msg.getProfile(sessionId);
		ArrayList graphsToLoad = new ArrayList<String>();
		Dataset ds = TDBFactory.assembleDataset(LongwellServlet.getAssemblerDirectory()+"/"+dataset[0]+"-assembler.ttl");
		//ds = TDBFactory.assembleDataset(LongwellServlet.getAssemblerDirectory() + "/folgaria-assembler.ttl");
		if (type.equals("1")) {
			String prefix = "PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>  "
					+ "PREFIX dul: <http://www.ontologydesignpatterns.org/ont/dul/DUL.owl#>  "
					+ "PREFIX an: <http://www.provincia.tn.it/ont/anagrafe.owl#>  "
					+ "PREFIX cu: <http://www.provincia.tn.it/ont/catasto_urbano.owl#> "
					+ "PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#> ";
			String queryString = prefix
					+ " select distinct ?person ?immobile ?categoriaCatastale ?ped ?rendita "
					+ "{{ graph "+getGraphs(dataset[0],"catasto_urbano")+" { "
					+ "?z dul:hasParticipant ?x,?y . "
					+ "?y a cu:Fabbricato . " + "?x a dul:Person . "
					+ "?x rdfs:label  ?person ."
					+ "?y rdfs:labels  ?immobile . " 
					+ " ?y  cu:categoriaCatastale ?categoriaCatastale. ?y  cu:renditaCatastale ?rendita. ?y  cu:numeroImmobile ?ped. "
					+ "}}} order by ?person";

			Query query = QueryFactory.create(queryString);
			s_logger.info(queryString);
			QueryExecution qe = QueryExecutionFactory.create(query, ds);
			ResultSet results = qe.execSelect();

			String person = "";
			String immobile = "";
			String categoriaCatastale = "";		
			String ped="";
			String rendita="";
			ArrayList<String> result = new ArrayList<String>();
			//Set result = new HashSet<String>();
			while (results.hasNext()) {
				QuerySolution record = results.next();
				person = record.get("person").toString();
				immobile = record.get("immobile").toString();
				categoriaCatastale = record.get("categoriaCatastale").toString();
				ped = record.get("ped").toString();
				rendita = record.get("rendita").toString();
				result.add(person + "|" + immobile + "#" +categoriaCatastale+"$"+ped+"?"+rendita);
				Collections.sort(result);
			}
			qe.close();

			vcContext.put("resultQuestion1", result);

			// profile.addData(model);
			try {
				msg.m_ve.mergeTemplate("templates/commands/resultQuestion1.vt",
						vcContext, msg.m_response.getWriter());
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		} else if (type.equals("2")) {
			String prefix = "PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>  "
					+ "PREFIX dul: <http://www.ontologydesignpatterns.org/ont/dul/DUL.owl#>  "
					+ "PREFIX an: <http://www.provincia.tn.it/ont/anagrafe.owl#>  "
					+ "PREFIX cu: <http://www.provincia.tn.it/ont/catasto_urbano.owl#> "
					+ "PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#> ";
			String queryString = prefix
					+ " select distinct ?fabbricato ?persona ?indirizzo ?categoriaCatastale ?ped ?rendita"
					+ "{"
					+ "graph "+getGraphs(dataset[0],"catasto_urbano")+" { "
					+ "?immobile cu:categoriaCatastale ?cat. "
					+ "?immobile rdfs:label ?label "
					+ "FILTER((?cat=\"A01\")||(?cat=\"AO2\")||(?cat=\"A03\")||(?cat=\"A04\")||(?cat=\"AO5\")||(?cat=\"A06\")||(?cat=\"A07\")||(?cat=\"AO8\")||(?cat=\"A09\")||(?cat=\"A11\")) "
					+ "} "
					+ "graph "+getGraphs(dataset[0],"catasto_urbano_titolarita")+" { "
					+ "?z dul:hasParticipant ?x,?immobile . "
					+ "?immobile a cu:Fabbricato . "
					+ "?x a an:PersonaFisica. "
					+ "?x rdfs:label  ?persona . "
					+ "?immobile rdfs:labels  ?fabbricato . "
					+ "} "
					+ "graph "+getGraphs(dataset[0],"anagrafe")+"  { "
					+ "?x  an:viaResidenza ?address. "
					+ "?address rdfs:label  ?indirizzo . "
					+ "} "
					+ "graph "+getGraphs(dataset[0],"catasto_urbano")+"{ "
					+ "?immobile  cu:haIndirizzo ?address. ?immobile  cu:categoriaCatastale ?categoriaCatastale. ?immobile  cu:numeroImmobile ?ped. ?immobile  cu:renditaCatastale ?rendita." + "} " + "} "
					+ "order by ?persona ";
			s_logger.info(queryString);
			Query query = QueryFactory.create(queryString);

			QueryExecution qe = QueryExecutionFactory.create(query, ds);
			ResultSet results = qe.execSelect();

			String persona = "";
			String fabbricato = "";
			String indirizzo = "";
			String categoriaCatastale = "";		
			String ped="";
			String rendita="";
			ArrayList<String> result = new ArrayList<String>();
			//Set result = new HashSet<String>();
			while (results.hasNext()) {
				QuerySolution record = results.next();
				indirizzo = record.get("indirizzo").toString();
				persona = record.get("persona").toString();
				categoriaCatastale = record.get("categoriaCatastale").toString();
				ped = record.get("ped").toString();
				rendita = record.get("rendita").toString();
				fabbricato = record.get("fabbricato").toString();
				result.add(persona + "|" + fabbricato + "@" + indirizzo+ "#" +categoriaCatastale+"$"+ped+"?"+rendita);
				Collections.sort(result);
			}
			qe.close();

			vcContext.put("resultQuestion2", result);

			// profile.addData(model);
			try {
				msg.m_ve.mergeTemplate("templates/commands/resultQuestion2.vt",
						vcContext, msg.m_response.getWriter());
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

		} else if (type.equals("3")) {
			String prefix = "PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>  "
					+ "PREFIX dul: <http://www.ontologydesignpatterns.org/ont/dul/DUL.owl#>  "
					+ "PREFIX an: <http://www.provincia.tn.it/ont/anagrafe.owl#>  "
					+ "PREFIX cu: <http://www.provincia.tn.it/ont/catasto_urbano.owl#> "
					+ "PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#> ";
			String queryString = prefix
					+ "select distinct ?fabbricato ?persona ?indirizzo ?categoriaCatastale ?ped ?rendita"
					+ "{ "
					+ "graph "+getGraphs(dataset[0],"catasto_urbano")+" { "
					+ "?immobile cu:categoriaCatastale ?cat. "
					+ " ?immobile  cu:categoriaCatastale ?categoriaCatastale. ?immobile  cu:numeroImmobile ?ped. ?immobile  cu:renditaCatastale ?rendita. "
					+ "?immobile rdfs:label ?label "
					+ "FILTER((?cat=\"A01\")||(?cat=\"AO2\")||(?cat=\"A03\")||(?cat=\"A04\")||(?cat=\"AO5\")||(?cat=\"A06\")||(?cat=\"A07\")||(?cat=\"AO8\")||(?cat=\"A09\")||(?cat=\"A11\")) "
					+ "} "
					+ "graph "+getGraphs(dataset[0],"catasto_urbano_titolarita")+"  { "
					+ "?z dul:hasParticipant ?x,?immobile . "
					+ "?immobile a cu:Fabbricato . "
					+ "?x a an:PersonaFisica. "
					+ "?x rdfs:label  ?persona . "
					+ "?immobile rdfs:labels  ?fabbricato . "
					+ "} "
					+ "{ "
					+ "graph "+getGraphs(dataset[0],"anagrafe")+"  { "
					+ "?x  an:viaResidenza ?address. "
					+ "?address rdfs:label  ?indirizzo . "
					+ "} "
					+ "FILTER(!EXISTS{ "
					+ "graph "+getGraphs(dataset[0],"catasto_urbano")+" { "
					+ "?immobile  cu:haIndirizzo ?indirizzo." + "} " + "} "
					+ ") " + "} " + "} " + "order by ?persona ";
			Query query = QueryFactory.create(queryString);
			
			QueryExecution qe = QueryExecutionFactory.create(query, ds);
			ResultSet results = qe.execSelect();
			s_logger.info(queryString);
			String persona = "";
			String fabbricato = "";
			String indirizzo = "";
			String categoriaCatastale = "";		
			String ped="";
			String rendita="";
			ArrayList<String> result = new ArrayList<String>();
			//Set result = new HashSet<String>();
			while (results.hasNext()) {
				QuerySolution record = results.next();
				indirizzo = record.get("indirizzo").toString();
				persona = record.get("persona").toString();
				categoriaCatastale = record.get("categoriaCatastale").toString();
				ped = record.get("ped").toString();
				rendita = record.get("rendita").toString();
				fabbricato = record.get("fabbricato").toString();
				result.add(persona + "|" + fabbricato + "@" + indirizzo+ "#" +categoriaCatastale+"$"+ped+"?"+rendita);
				Collections.sort(result);
			}
			qe.close();

			vcContext.put("resultQuestion3", result);

			// profile.addData(model);
			try {
				msg.m_ve.mergeTemplate("templates/commands/resultQuestion3.vt",
						vcContext, msg.m_response.getWriter());
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

		} else if (type.equals("4")) {

		} else if (type.equals("5")) {
			String prefix = "PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>  "
					+ "PREFIX dul: <http://www.ontologydesignpatterns.org/ont/dul/DUL.owl#>  "
					+ "PREFIX an: <http://www.provincia.tn.it/ont/anagrafe.owl#>  "
					+ "PREFIX cu: <http://www.provincia.tn.it/ont/catasto_urbano.owl#> "
					+ "PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#> ";
			String queryString = prefix
					+ "select ?categoria ?fabbricato ?impresa "
					+ "{ "
					+ " graph "+getGraphs(dataset[0],"catasto_urbano")+" { "
					+ "?immobile cu:categoriaCatastale ?categoria. "
					+ "?immobile rdfs:label ?label "
					+ "} "
					+ "graph "+getGraphs(dataset[0],"catasto_urbano_titolarita")+"  { "
					+ "?z dul:hasParticipant ?x,?immobile . "
					+ "?immobile a cu:Fabbricato . "
					+ "?x a an:PersonaGiuridica . "
					+ "?x rdfs:label  ?impresa . "
					+ "?immobile rdfs:labels  ?fabbricato . " + "} " + "} "
					+ "order by ?categoria ";
			Query query = QueryFactory.create(queryString);

			QueryExecution qe = QueryExecutionFactory.create(query, ds);
			ResultSet results = qe.execSelect();

			String categoria = "";
			String fabbricato = "";
			String impresa = "";
			ArrayList<String> result = new ArrayList<String>();
			//Set result = new HashSet<String>();
			while (results.hasNext()) {
				QuerySolution record = results.next();
				categoria = record.get("categoria").toString();
				fabbricato = record.get("fabbricato").toString();
				impresa = record.get("impresa").toString();
				result.add(categoria + "|" + fabbricato + "@" + impresa);
				Collections.sort(result);
			}
			qe.close();

			vcContext.put("resultQuestion5", result);

			// profile.addData(model);
			try {
				msg.m_ve.mergeTemplate("templates/commands/resultQuestion5.vt",
						vcContext, msg.m_response.getWriter());
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

		} else if (type.equals("6")) {

		} else if (type.equals("7")) {
			String prefix = "PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>  "
					+ "PREFIX dul: <http://www.ontologydesignpatterns.org/ont/dul/DUL.owl#>  "
					+ "PREFIX an: <http://www.provincia.tn.it/ont/anagrafe.owl#>  "
					+ "PREFIX cu: <http://www.provincia.tn.it/ont/catasto_urbano.owl#> "
					+ "PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#> "
					+ "PREFIX ici: <http://www.provincia.tn.it/ont/versamenti_ici.owl#> ";
			String queryString = prefix
					+ " select distinct ?fabbricato ?persona ?categoriaCatastale ?ped ?rendita "
					+ " { "
					+ " graph "+getGraphs(dataset[0],"catasto_urbano")+" { "
					+ " ?immobile cu:categoriaCatastale ?cat. "
					+ " ?immobile  cu:categoriaCatastale ?categoriaCatastale. ?immobile  cu:numeroImmobile ?ped. ?immobile  cu:renditaCatastale ?rendita. "
					+ " ?immobile rdfs:label ?label "
					+ " FILTER((?cat=\"A01\")||(?cat=\"AO2\")||(?cat=\"A03\")||(?cat=\"A04\")||(?cat=\"AO5\")||(?cat=\"A06\")||(?cat=\"A07\")||(?cat=\"AO8\")||(?cat=\"A09\")||(?cat=\"A11\")) "
					+ " } "
					+ " graph "+getGraphs(dataset[0],"catasto_urbano_titolarita")+"  { "
					+ " ?z dul:hasParticipant ?x,?immobile . "
					+ " ?immobile a cu:Fabbricato . "
					+ " ?x a an:PersonaFisica. "
					+ " ?x rdfs:label  ?person . "
					+ " ?immobile rdfs:labels  ?fabbricato . "
					+ " } "
					+ " { "
					+ " graph "+getGraphs(dataset[0],"anagrafe")+"  { "
					+ " ?x  an:viaResidenza ?address. "
					+ " ?address rdfs:label  ?addresslabelPersona . "
					+ " } "
					+ " FILTER(!EXISTS{ "
					+ " graph "+getGraphs(dataset[0],"catasto_urbano")+" { "
					+ " ?immobile  cu:haIndirizzo ?address. "
					+ " } "
					+ " } "
					+ " ) "
					+ " } "
					+ " graph "+getGraphs(dataset[0],"catasto_urbano_titolarita")+"  { "
					+ " ?z dul:hasParticipant ?x,?immobile . "
					+ " ?immobile a cu:Fabbricato . "
					+ " ?x a an:PersonaFisica. "
					+ " ?x rdfs:label  ?persona . "
					+ " ?immobile rdfs:labels  ?label . "
					+ " } "
					+ " graph "+getGraphs(dataset[0],"versamenti_ici")+"  { "
					+ " ?event dul:hasParticipant ?x. "
					+ " ?describes dul:describes ?event . "
					+ " ?describes ici:importoAltriFabbricati ?senza . "
					+ " FILTER(?senza=\"0\") " + " } " + " }";

			Query query = QueryFactory.create(queryString);

			QueryExecution qe = QueryExecutionFactory.create(query, ds);
			
			
			ResultSet results = qe.execSelect();
			s_logger.info(queryString);
			String person = "";
			String immobile = "";
			String categoriaCatastale = "";
			String ped = "";
			String rendita="";
			ArrayList<String> result = new ArrayList<String>();
			//Set result = new HashSet<String>();
			while (results.hasNext()) {
				QuerySolution record = results.next();
				person = record.get("persona").toString();
				immobile = record.get("fabbricato").toString();
				categoriaCatastale = record.get("categoriaCatastale").toString();
				ped = record.get("ped").toString();
				rendita = record.get("rendita").toString();
				result.add(person + "|" + immobile + "#" +categoriaCatastale+"$"+ped+"?"+rendita);
				Collections.sort(result);
			}
			qe.close();

			vcContext.put("resultQuestion7", result);

			// profile.addData(model);
			try {
				msg.m_ve.mergeTemplate("templates/commands/resultQuestion7.vt",
						vcContext, msg.m_response.getWriter());
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	private String getGraphs(String dataset, String graphType) {
        Properties properties = new Properties();
        if(graphsMappingFile==null)
        	graphsMappingFile = new File(LongwellServer.getGraphsMappingPath());
        
        try {
			FileInputStream fisConf = new FileInputStream(graphsMappingFile);
			properties.load(fisConf);
			fisConf.close();
		} catch (Exception e) {
            s_logger.error("Failed to read properties file", e);
            System.exit(1);
		}
    	return  properties.getProperty(dataset+"."+graphType);
	}
}